import requests,json
from datetime import datetime, timedelta
import decimal
import logging

 
import time 
from dateutil.relativedelta import relativedelta

formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger('mylog')
logger.setLevel(logging.DEBUG)
ch = logging.StreamHandler()
ch.setFormatter(formatter)
logger.addHandler(ch)



tabId_某月考勤日明细  =  'meionvotj4a7fr4' # 旧 m32i8ki85w1uflw
tabId_考勤人员表  =  'mu6gcbj1zq8dg0v' # 旧 mkccrm1f5xnvsyt
tabId_每月假期与工作日  = 'm3h341x2p6wyftc'

import 补考勤报告   

dbapi_addr = 'l-noco.ouqiyj.com:8000'
dbapi_addr = '192.168.0.123:8123'

bk = 补考勤报告.补考勤报告()

def 企业微信更新密钥到多维表格():

    corpid = "ww9c0c7927b70a1368"
    corpsecret = "liSANMa-e1atIqLlKSIQKpsB1YaZ0HbLBiaHGednV_A"
    url = f"https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid={corpid}&corpsecret={corpsecret}"
    response = requests.post(url)
    keys = json.loads(response.text)
    today = datetime.today()
    # 获取两位数的日期（几号）
    day_two_digits = today.strftime("%Y-%m-%d %H:%M:%S")  # 例如：15
    if keys['errcode'] != 0 :
        row = {"创建于":day_two_digits,"错误情况":keys['errmsg'] }
    else:
        timestamp = int(datetime.now() .timestamp())+keys['expires_in']
        dt_object = datetime.fromtimestamp(timestamp)
        formatted = dt_object.strftime("%Y-%m-%d %H:%M:%S")
        row = {"创建于":day_two_digits,"错误情况":keys['errmsg'],"密钥":keys['access_token'] ,"到期时间戳":timestamp,"到期时间":formatted}
    
    多维表格保存('mitdc13jt521orr',row)
    # mitdc13jt521orr
    return keys['errmsg']


def 多维表格保存(tabId ,row ):
    """  """
    url = f"http://{dbapi_addr}/api/v2/tables/{tabId}/records"
    headers = {"xc-token": "_tuWJFjsFOBeHUqmR-FRlVNZiIOFHTtTmB1W-lp1"}
    response = requests.post(url, headers=headers, json=row)
    return json.loads(response.text)

def 多维表格更改(tabId ,row ):
    """  """
    url = f"http://{dbapi_addr}/api/v2/tables/{tabId}/records"
    headers = {"xc-token": "_tuWJFjsFOBeHUqmR-FRlVNZiIOFHTtTmB1W-lp1"}
    response = requests.patch(url, headers=headers, json=row)
    return json.loads(response.text)

def 多维表格查询(tabId ,params ):
    """   """
    url = f"http://{dbapi_addr}/api/v2/tables/{tabId}/records"
    headers = {"xc-token": "_tuWJFjsFOBeHUqmR-FRlVNZiIOFHTtTmB1W-lp1"}
    response = requests.get(url, headers=headers,params=params)
    return json.loads(response.text)

def 删除一条(tabId ,data):
    """ 读取表格  """
    url = f"http://{dbapi_addr}/api/v2/tables/{tabId}/records"
    xc_token = "_tuWJFjsFOBeHUqmR-FRlVNZiIOFHTtTmB1W-lp1"
    headers = {"xc-token": xc_token}
 
    response = requests.delete(url, headers=headers, json=data)
    tabs = json.loads(response.text)
    return tabs


def 一页表格数据(tabId ,page=0,where=None):
    """ 读取表格  """
    url = f"http://{dbapi_addr}/api/v2/tables/{tabId}/records"
    xc_token = "_tuWJFjsFOBeHUqmR-FRlVNZiIOFHTtTmB1W-lp1"
    headers = {"xc-token": xc_token}
    params = {
        "offset": page * 1000 ,
        "limit": 1000
    }
    if(where is not None ):
        params['where'] = where
    response = requests.get(url, headers=headers,params=params)
    tabs = json.loads(response.text)
    return tabs

def 表格所有行(tabId,where=None):
    rows = []
    page = 0 
    while True:
        tabs = 一页表格数据(tabId,page,where)
        rows = rows+tabs["list"]

        isend = (tabs["pageInfo"] ['pageSize'] *  tabs["pageInfo"] ['page'] ) >= tabs["pageInfo"] ['totalRows']
        if isend:
            break
        page = page +1 
    return rows


def 单人日考勤入库(基础数据,日历):
    """     """
    名字 = 基础数据['oa/T_USER@用户'][0]['NAME']
    电话 = 基础数据['oa/T_USER@用户'][0]['手机']
    for r in set(日历) :
        #print(r)
        考勤原始 = 获取单日考勤原始数据(r,基础数据)
        公出原始 = 获取单日公出原始数据(r,基础数据)
        请假原始 = 获取单日请假原始数据(r,基础数据)
        补考勤原始 = 获取单日补考勤原始数据(r,基础数据)

        params = {
            "人名":名字,
            '日期':r,
            "星期":datetime.strptime(r, "%Y-%m-%d").weekday()+1,
            '电话':电话,
            "考勤原始":    json.dumps(考勤原始,ensure_ascii=False),
            "补考勤原始":    json.dumps(补考勤原始,ensure_ascii=False) ,
            '公出原始' :    json.dumps(公出原始,ensure_ascii=False),
            '请假原始' :    json.dumps( 请假原始 ,ensure_ascii=False)

        }
        # pgsql 表 meionvotj4a7fr4
        多维表格保存('meionvotj4a7fr4',params)
        
    return 


def 获取单日考勤原始数据(日期,基础数据):
    """  """
    v = [ r['CHECKTIME'] for r in 基础数据['kq/CHECKINOUT@考情记录'] if r['CHECKTIME'][:10] == 日期 ]
    return v


def 获取单日补考勤原始数据(日期,基础数据):
    """  """
    v = [ r['ITEM_ATTDATE'] for r in 基础数据['oa/TLK_ATTENDANCEDETAIL@补考勤'] if r['ITEM_ATTDATE'][:10] == 日期 ]
    return v


def 获取单日请假原始数据(日期,基础数据):
    """  经过多少个考勤点   返货原始数据概要"""
    all_list = []
     
    #所有公出
    for r in 基础数据['oa/TLK_APPLY_LEAVE@请假'] :
        
        all_list = all_list + bk._记录转假勤(
            r['开始时间']
            ,r['结束时间']
            ,[r['开始时间'],r['结束时间'],r['理由']]
        )
        
    
    v = [ r['类型'] for r in all_list if r['CHECKTIME'][:10] == 日期 ]
    return v[0] if len(v) else None


def 获取单日公出原始数据(日期,基础数据):
    """  经过多少个考勤点   返货原始数据概要"""
    all_list = []
     
    #所有公出
    for r in 基础数据['oa/TLK_BUSINESS_PUBLIC_TRIP@公出'] :
        
        all_list = all_list + bk._记录转假勤(
            r['ITEM_TRAVEL_ST']
            ,r['ITEM_TRAVEL_OT']
            ,[r['ITEM_TRAVEL_ST'],r['ITEM_TRAVEL_OT'],r['理由']]
        )
        
    
    v = [ r['类型'] for r in all_list if r['CHECKTIME'][:10] == 日期 ]
    return v[0] if len(v) else None


def 日基础考勤数据入库(年,月,类别="单休制人员"):
    标准日历_list = bk.标准日历(int(年),int(月))
    日期 = [ r['日期'] for r in 标准日历_list ]
    日历 = set(日期)
    #---
    单休制人员表 = 表格所有行(tabId_考勤人员表,f"(类别,eq,{类别})") #('mkccrm1f5xnvsyt')
    logger.info(f'执行报表人数 {len(单休制人员表)}')
    for r in 单休制人员表:
        执行结果 = 'ok' + datetime.now().strftime("%Y-%m-%d")
        姓名 = r['姓名']
        
        基础数据 = bk.getKqData(姓名,月,年)
        if len(基础数据['oa/T_USER@用户']) <1 :
            continue
        电话 = 基础数据['oa/T_USER@用户'][0]['手机']
        登入ID = 基础数据['oa/T_USER@用户'][0]['登入ID']

        if( len( 基础数据['oa/T_USER@用户']  ) == 0 ):
            执行结果 = '无此用户'
        elif len( 基础数据['kq/CHECKINOUT@考情记录']  ) == 0 :    
            执行结果 = '无考勤记录'
        else:
            单人日考勤入库(基础数据,日历)#日明细

        # id or Id
        data = {'id': r['id'],"电话":电话,"登入ID":登入ID,"执行结果":执行结果}
        t = 多维表格更改(tabId_考勤人员表,data)
        #logger.info(f" 更新考勤人员信息：结果 {t}")

    return 单休制人员表
#----------------
def 当天是否假期(row,某月假期表):
    """ 返回 [假期, 上班] """
    
    for 假期 in 某月假期表:
        #print( row['星期'] ,假期['星期'],row['日期'] , 假期['完整日期'])
        if row['星期'] == 假期['星期']:
            return '假期'
        if row['日期'] == 假期['完整日期']:
            return '假期'
    return '上班'
    
def 填入上下班考勤(row,日考勤列表):
    """  """
    日期  = row['日期']
    d上班 = 日期 +" 08:00:00"
    d下班 = 日期 +" 17:00:00"

    #----
    #日考勤列表 = json.loads(row['考勤原始'].replace("'", '"'))
    日考勤列表.sort()
    上班 = None
    下班 = None
 
    for r in 日考勤列表:
        rtime = datetime.strptime(r, "%Y-%m-%d %H:%M:%S")
        if   r < 日期+" 08:10:00"   and r  > 日期+" 00:00:00":
            if r <= d上班 :
                上班 = 'ok'
            else:
                time1 = datetime.strptime(d上班, "%Y-%m-%d %H:%M:%S")
                上班 = int(abs(time1 - rtime).total_seconds() / 60)
        if   r >=  日期+" 16:50:00"     and r  < 日期+" 23:59:00":
            if r >= d上班 :
                下班 = 'ok'
            else:
                time1 = datetime.strptime(d下班, "%Y-%m-%d %H:%M:%S")
                下班 = int(abs(time1 - rtime).total_seconds() / 60)
    return 上班,下班

def 填入中午2个考勤(日考勤列表):
    """  观察前后一小时 """
    #日期  = row['日期']
    d中午下班 =  "11:30:00"
    d中午上班 =  "12:30:00"
    #----
    #日考勤列表 = json.loads(row['考勤原始'].replace("'", '"'))
    日考勤列表.sort()
    中午下班 = None
    中午上班 = None
    #--中午两条
    tempList =  []
    for r in 日考勤列表:
        one = r[11:]
        if  one >= d中午下班   and  one  <= d中午上班:
            tempList.append(one)
    if len(tempList)>=2:
        return 'ok','ok'
    #---标准时间
 
    if len(tempList)==1 and tempList[0]==d中午下班: 
        return 'ok',''
    if len(tempList)==1 and tempList[0]==d中午上班: 
        return '','ok'
 
    #---
    done = None
    for r in 日考勤列表:
        one = r[11:]
        rtime = datetime.strptime(one, "%H:%M:%S")
        if   one > "11:20:00"   and one  < "12:40:00":
            if one >= d中午下班 and  one  <= d中午上班: 
                中午下班 = 'ok'
                done = r
                break
            else:
                time1 = datetime.strptime(d中午下班, "%H:%M:%S")
                中午下班 = int(abs(time1 - rtime).total_seconds() / 60)
                done = r
                break
    if done:
        日考勤列表.remove(r)
    for r in 日考勤列表:
        one = r[11:]
        rtime = datetime.strptime(one, "%H:%M:%S")
        if   one > "11:20:00"   and one  < "12:40:00":
            if one >= d中午下班 and  one  <= d中午上班: 
                中午上班 = 'ok'
            else:
                time1 = datetime.strptime(d中午上班, "%H:%M:%S")
                中午上班 = int(abs(time1 - rtime).total_seconds() / 60)
    
    return 中午下班,中午上班
    
def 公出转考勤(row):
    """   """
    
    公出原始 = row['公出原始']
    #print(type(公出原始))
    if 公出原始 is None or 公出原始=='null':
        return  []
    公出原始 = json.loads(row['公出原始'])
    公出列表  = bk._记录转假勤(公出原始[0],公出原始[1],公出原始[2] )
    
    return [r['CHECKTIME'] for r in 公出列表] 
 
def 请假转考勤(row):
    """   """
    
    请假原始 = row['请假原始']
    #print(type(公出原始))
    if 请假原始 is None or 请假原始=='null':
        return  []
    请假原始 = json.loads(row['请假原始'])
    请假列表  = bk._记录转假勤(请假原始[0],请假原始[1],请假原始[2] )
    
    return [r['CHECKTIME'] for r in 请假列表] 

def 出勤状态(nrow):
    """  """
    k1 = [nrow['上班'],nrow['补上班'],nrow['公上班'],nrow['请假上班']]
    k2 = [nrow['中午下'],nrow['补中午下'],nrow['公中午下'],nrow['请假中午下']]
    k3 = [nrow['中午上'],nrow['补中午上'],nrow['公中午上'],nrow['请假中午上']]
    k4 = [nrow['下班'],nrow['补下班'],nrow['公下班'],nrow['请假下班']]
    
    #print(k1,k2,k3,k4)
    i1 = 'ok' if  len( [i for i in k1 if i is not None] )>0  else''  
    i2 = 'ok' if  len( [i for i in k2 if i is not None] )>0  else''  
    i3 = 'ok' if  len( [i for i in k3 if i is not None] )>0  else''  
    i4 = 'ok' if  len( [i for i in k4 if i is not None] )>0  else''  
    出勤状态 = [i1,i2,i3,i4]

    return json.dumps(出勤状态  ,ensure_ascii=False)

def 扣分情况(nrow):
    """  """
    实际打卡 = [nrow['上班'],nrow['中午下'],nrow['中午上'],nrow['下班']]
    扣分 = 0
    for r in 实际打卡:
        if r is None or r =='null' or  r =='ok':
            continue
        扣分 = 扣分 + int(r)
    return 扣分

def 日补卡建议(nrow,row,打卡周期列表=None):
    """ """
    if 打卡周期列表 is None:
        打卡周期列表=["08:00:00","11:30:00","12:30:00","17:00:00"]
    if nrow['班假'] == '假期':
        return '[]'
    补卡list = []
    打卡次数 = len(打卡周期列表)
    出勤状态 = json.loads( nrow['出勤状态'] )#字符串转列表
    for i in range(打卡次数):
        if 出勤状态[i] != 'ok':
            补卡list.append( row['日期']+' '+打卡周期列表[i])
    return json.dumps(  补卡list ,ensure_ascii=False)
    
def 每日概述(nrow,打卡周期列表=None):
    """ 纯文本描述请 """
    info = []
    if 打卡周期列表 is None:
        打卡周期列表=["08:00:00","11:30:00","12:30:00","17:00:00"]

    打卡次数 = len(打卡周期列表)
    #nrow['每日概述'] = ''
    实际打卡 = [nrow['上班'],nrow['中午下'],nrow['中午上'],nrow['下班']]
    实际打卡数 =  len( [i for i in 实际打卡 if i is not None] )
    出勤状态 = json.loads( nrow['出勤状态'] )#字符串转列表
    需要补卡条数 = 打卡次数-len( [i for i in 出勤状态 if i == 'ok'] )
    info.append( f'实际打卡数:{实际打卡数}' )
    info.append(f'需要补卡条数:{需要补卡条数}' )
    if nrow['班假'] == '假期':
        info.append(f'当天:休息' )
    #--请假时长
    return json.dumps( info   ,ensure_ascii=False)


def 处理有表格数据为建议(年,月):
    月 = str(月).zfill(2)
    params = {
    "where": f"(年份,eq,{年})~and(月份,eq,{月})",
    }

    某月假期表 = 多维表格查询(tabId_每月假期与工作日,params)
    某月考勤日明细 = 表格所有行(tabId_某月考勤日明细,f"(日期,like,{年}-{月}-%)")
    logger.info(f'需要更新的行数 {len(某月考勤日明细)}')
    for row in 某月考勤日明细:
        nrow = {}
        日考勤列表 = json.loads(row['考勤原始'])
        nrow['班假'] = 当天是否假期(row,某月假期表['list'])
        nrow['上班'] ,nrow['下班'] = 填入上下班考勤(row ,日考勤列表)
        nrow['中午下'] ,nrow['中午上'] = 填入中午2个考勤(日考勤列表)
        #--补卡
        补卡列表 = json.loads(row['补考勤原始'])
        nrow['补上班'] ,nrow['补下班'] = 填入上下班考勤(row ,补卡列表)
        nrow['补中午下'] ,nrow['补中午上'] = 填入中午2个考勤(补卡列表)
        #--公出
        公出列表 = 公出转考勤(row)

        nrow['公上班'] ,nrow['公下班'] = 填入上下班考勤(row ,公出列表)
        nrow['公中午下'] ,nrow['公中午上'] = 填入中午2个考勤(公出列表)
         #--请假
        请假列表 = 请假转考勤(row)

        nrow['请假上班'] ,nrow['请假下班'] = 填入上下班考勤(row ,请假列表)
        nrow['请假中午下'] ,nrow['请假中午上'] = 填入中午2个考勤(请假列表)
        #---
        nrow['出勤状态']  = 出勤状态(nrow)
        nrow['扣分钟数']  = 扣分情况(nrow)
        nrow['补卡建议']  = 日补卡建议(nrow,row,None)
        nrow['每日概述']  = 每日概述(nrow,None)
        #print(nrow)
        #--回写
        nrow['id'] = row['id']
        t = 多维表格更改(tabId_某月考勤日明细,nrow)
        #logger.info(f" 处理有表格数据为建议:结果 {t}")
        
def 循环删除年月数据行(year,month):
    """  """
    month = str(month).zfill(2)
    某月考勤日明细 = 表格所有行(tabId_某月考勤日明细,f"(日期,like,{year}-{month}-%)")
    logger.info(f'需要删除的行数 ： {len(某月考勤日明细)}')
    for row in 某月考勤日明细:
        nrow = {}
        #--逐条删除
        nrow['id'] = row['id']
        删除一条(tabId_某月考勤日明细,nrow)


def  返回当天补卡建议(name,year:int,month:int,day:int):
    """  day 日期 """
    month = str(month).zfill(2)
    day = str(day).zfill(2)

    某人某月日明细 = 表格所有行(tabId_某月考勤日明细, f'(日期,like,{year}-{month}-{day})~and(人名,eq,{name})') 
    
    补list = []
    for row in 某人某月日明细:
        补list = 补list + json.loads(row['补卡建议'])
        #print(row['补卡建议'])
    补list.sort()

    补list = [ r for r in 补list if r[0:10] <= f"{year}-{month}-{day}"]
    logger.info(f'{name} 明细 {len(某人某月日明细)}条，补卡行数：{len(补list)}')
    return  补list



def  返回补卡建议(name,year:int,month:int,day:int):
    """  day 截止日期 """
    month = str(month).zfill(2)
    day = str(day).zfill(2)

    某人某月日明细 = 表格所有行(tabId_某月考勤日明细, f'(日期,like,{year}-{month}-%)~and(人名,eq,{name})~and(班假,eq,上班)') 
    
    补list = []
    for row in 某人某月日明细:
        补list = 补list + json.loads(row['补卡建议'])
        #print(row['补卡建议'])
    补list.sort()

    补list = [ r for r in 补list if r[0:10] <= f"{year}-{month}-{day}"]
    logger.info(f'{name} 明细 {len(某人某月日明细)}条，补卡行数：{len(补list)}')
    return  补list


if __name__ == "__main__":
    today = datetime.today()
    day_two_digits = today.strftime("%d")  # 例如：15
    年=2025
    月=11
    循环删除年月数据行(年,月)
    日基础考勤数据入库(年,月)
    处理有表格数据为建议(年,月)
    补卡数据 = 返回补卡建议('蔡金凯',年,月)
    logger.info(f"补卡数据:{补卡数据},日：{day_two_digits}")
    
